File-based Data Sources

File based data sources for data models are usually "local" files that a user wishes to either model directly or included to embellish configured data sources. File based data sources require no pre-configuration and offer few limitations to the business user.

File source include Excel, Access and Text files. It also includes Excel files that contain PowerPivot models that will be used to generate a model directly.

Using a File Data Source

To add a file, select the file type and then click the add button in the right hand panel (blue highlight below)

You will be prompted with a small pop-up dialog to either import the file into BI Office (purple arrow below) or to reference a file on a shared network drive (red arrow).

NOTE: If you upload, the file will be uploaded in chunks and can take some time as it is pulled over the Internet. There is also an upload file size limit set by your administrator.

You can add as many file sources in a single model as needed. If the file is text based, you may need to provide further input into how the text file should be parsed when processed in the model. See below for more on text files.

Shared File Data Sources

Shared files offer an interesting option for importing file based data sources. Instead of uploading the file statically to BI Office, the user instead provides a pointer to its location on the network. When the model is processed the file is read in and included in the model. This offers the option for file based models to auto update themselves with new data when the model is set to auto-refresh in BI Office, by simply replacing the source in its networked location with an updated file.

IMPORTANT: The shared file must use an "UNC" file path format to the shared directory and file on the network. The directory must also be visible both by the machines hosting BI Office and the SSAS tabular instance. Click 'Check Path' to ensure the format is correct.

Text Files

Once the file is uploaded, the "Text Loader" wizard will appear.

The wizard will automatically select the Delimiter(outlined in orange). If the selection is wrong, you can manually select a different one. (A delimiter is the character within the text that separates the data.)

Select the checkbox (indicated with the blue arrow) to make the first row from the text as its header.

The wizard will automatically make selections for the encoding and culture settings, but you can adjust them through Advanced Settings (red arrow). Click the Advanced Settings button to get the pop-up window (see below) where you can manually change the settings.

PowerPivot

The PowerPivot Uploader is part of the Data Model as it allows end users to take their own data, in this case, PowerPivot Excel Spreadsheets and 'upgrade' them to tabular cubes hosted on a SQL Server 2012/14/16 Tabular Server. The upgrade affords the following benefits:

• The tabular cubes can then be input and analyzed in BI Office.
• The tabular cubes will operate on a more powerful infrastructure. (Generally, servers are more powerful than desktops.)

BI Office analytics delivers powerful capabilities and content sharing amongst a group of users or the enterprise at large. BI Office can be used for self-service BI and enterprise BI without a change in technologies, client tools or infrastructure.

NOTE:

PowerPivot files cannot use the Shared file source capability due to limitations in SSAS.

PowerPivot files cannot be combined with other files in the modeling process since it already represents the completed model design and its data.

Once a PowerPivot file is uploaded, the table, column, relationship and hierarchy stages are skipped. Users are instead sent to the security settings and finalization stages for the import process to be completed.